#!/usr/bin/env node

const Koa = require('koa');
const Router = require('koa-router');
const BodyParser = require('koa-bodyparser');
const mysql = require('mysql2/promise');
const loadRouters = require('require-directory');
const koaBody = require('koa-body')
const fs = require('fs')
const path = require('path')
const mime = require('mime-types');

const app = new Koa();
const router = new Router();
const bodyParser = new BodyParser();

const connection= mysql.createPool({
  host:'localhost',
  user:'root',
  password:'18331667285',
  database:'test'
})

//设置跨域资源请求的问题
app.use(async (ctx,next) => {
  ctx.set('Access-Control-Allow-Origin', '*');
  ctx.set('Access-Control-Allow-Headers','Content-Type, Content-Length, Authorization, Accept, X-Requested-With,yourHeaderFeild'
    );
  ctx.set('Access-Control-Allow-Methods', 'PUT, POST, GET, DELETE');
  if(ctx.method == 'OPTIONS'){
    ctx.body = 200;
  }else{
    await next();  }
})
//设置上传文件的问题
app.use(koaBody({
  multipart:true
}))

/******************获得数据*************************/
//用户
router.get('/',async (ctx,next) => {
  let sql = 'select * from user';
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
  
})

router.get('/city',async(ctx,next) => {
  let sql = 'select * from city'
  let res = await connection.execute(sql);
  let arr = res[0];
  ctx.body = JSON.stringify(arr);
})

//城市文化列表推荐
router.get('/city/citycul',async(ctx,next) => {
  let sql = 'select * from culture'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})
//景点推荐
router.get('/city/interest',async(ctx,next) => {
  let sql = 'select * from place'
  let res = await connection.execute(sql);
  console.log(res[0])
  ctx.body = JSON.stringify(res[0]);
})

//周边推荐管理
router.get('/city/enviro',async(ctx,next) => {
  let sql = 'select * from recommend'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//美食推荐管理
router.get('/city/food',async(ctx,next) => {
  let sql = 'select * from food'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//酒店推荐管理
router.get('/city/hotel',async(ctx,next) => {
  let sql = 'select * from pub'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//帖子管理
router.get('/post',async(ctx,next) => {
  let sql = 'select * from share'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})
//系统管理
router.get('/admin',async(ctx,next) => {
  let sql = 'select adminImg,adminName,adminAddr,adminTno from admin';
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

/***************新建表单中的insert*******************************/
//用户管理
router.post('/',async (ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  let arr = []
  for(let i in obj){
    if(i == 'upload'){
      arr.push('UserImg')
      str = str + `'${obj[i][0].response.addr}',`
    }else{
      arr.push(i)
      str = str + `'${obj[i]}',`
    }
  }
  let x = str.slice(0,str.length-1);
  let sql = `INSERT INTO user(${arr}) VALUES(${x}) `
  await connection.execute(sql)
  ctx.body = JSON.stringify("success");
})
//城市文化列表管理
router.post('/city/citycul',async (ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  let arr = []
  for(let i in obj){
    if(i == 'upload'){
      arr.push('cultureImg')
      if(obj[i].length == 1){
        str = str + `'${obj[i][0].response.addr}',`
      }else{
        let str0 = '';
        for(let j = 0 ; j< obj[i].length;j++){
          str0 = str0 + `${obj[i][j].response.addr};`
        }
        console.log(str0)
        str = str + `'${str0}',`
      }
    }else{
      arr.push(i)
      str = str + `'${obj[i]}',`
    }
  }
  let x = str.slice(0,str.length-1);
  let sql = `INSERT INTO culture(${arr}) VALUES(${x}) `
  console.log(sql)
  await connection.execute(sql); 
  ctx.body = JSON.stringify("success");
})
//景点推荐
router.post('/city/interest',async(ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  let arr = []
  for(let i in obj){
    if(i == 'upload'){
      arr.push('placeImg')
      if(obj[i].length == 1){
        str = str + `'${obj[i][0].response.addr}',`
      }else{
        let str0 = '';
        for(let j = 0 ; j< obj[i].length;j++){
          str0 = str0 + `${obj[i][j].response.addr};`
        }
        console.log(str0)
        str = str + `'${str0}'`
      }
    }else{
      arr.push(i)
      str = str + `'${obj[i]}',`
    }
    
    
  }
  let x = str.slice(0,str.length-1);
  let sql = `INSERT INTO place(${arr}) VALUES (${x})`
  console.log(sql)
  await connection.execute(sql); 
  ctx.body = JSON.stringify("success");
})
//周边推荐
router.post('/city/enviro',async(ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  let arr = []
  for(let i in obj){
    if(i == 'upload'){
      arr.push('recomImg')
      if(obj[i].length == 1){
        str = str + `'${obj[i][0].response.addr}',`
      }else{
        let str0 = '';
        for(let j = 0 ; j< obj[i].length;j++){
          str0 = str0 + `${obj[i][j].response.addr};`
        }
        console.log(str0)
        str = str + `'${str0}'`
      }
    }else{
      arr.push(i)
      str = str + `'${obj[i]}',`
    }
  }
  let x = str.slice(0,str.length-1);
  let sql = `INSERT INTO recommend(${arr}) VALUES(${x})`
  await connection.execute(sql); 
  ctx.body = JSON.stringify("success");
})
//美食推荐管理
router.post('/city/food',async(ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  let arr = []
  for(let i in obj){
    if(i == 'upload'){
      arr.push('foodImg')
      if(obj[i].length == 1){
        str = str + `'${obj[i][0].response.addr}',`
      }else{
        let str0 = '';
        for(let j = 0 ; j< obj[i].length;j++){
          str0 = str0 + `${obj[i][j].response.addr};`
        }
        console.log(str0)
        str = str + `'${str0}'`
      }
    }else{
      arr.push(i)
      str = str + `'${obj[i]}',`
    }
  }
  
  let x = str.slice(0,str.length-1);
  let sql = `INSERT INTO food(${arr}) VALUES (${x})`
  await connection.execute(sql); 
  ctx.body = JSON.stringify("success");
})
//酒店推荐管理
router.post('/city/hotel',async(ctx,next) => {
  let obj = ctx.request.body;
  console.log(obj)
  let str = '';
  let arr = []
  for(let i in obj){
    if(i == 'upload'){
      arr.push('pubImg')
      if(obj[i].length == 1){
        str = str + `'${obj[i][0].response.addr}',`
      }else{
        let str0 = '';
        for(let j = 0 ; j< obj[i].length;j++){
          str0 = str0 + `${obj[i][j].response.addr};`
        }
        console.log(str0)
        str = str + `'${str0}'`
      }
      
    }else{
      arr.push(i)
      str = str + `'${obj[i]}',`
    }
  }

  console.log(arr);
  let x = str.slice(0,str.length-1);
  let sql = `INSERT INTO pub(${arr}) VALUES (${x})`
  // await connection.execute(sql); 
  console.log(sql)
  ctx.body = JSON.stringify("success");
})
//帖子管理
// router.post('/post',async(ctx,next) => {
//   let obj = ctx.request.body;
//   console.log(obj)
//   let sql = `INSERT INTO share (${arr}) VALUES (${x})`
//   await connection.execute(sql); 
//   ctx.body = JSON.stringify("success");
// })


/****************删除的操作*****************************/
router.delete('/:Userwxno',async (ctx,next) => {
  let obj = ctx.params;
  let Userwxno = obj.Userwxno;
  console.log(Userwxno)
  let sql = `DELETE FROM user WHERE Userwxno = '${Userwxno}'`
    console.log(sql)
  await connection.execute(sql)
  ctx.body = JSON.stringify("success");
})

//城市文化列表管理
router.delete('/city/citycul/:cultureID/:cityID',async (ctx,next) => {
  let obj = ctx.params;
  let cultureID = obj.cultureID;
  let cityID = obj.cityID;
  let sql = `DELETE FROM culture WHERE cultureID = '${cultureID}' and cityID = '${cityID}'`
  await connection.execute(sql); 
  ctx.body = JSON.stringify("success");
})
//景点推荐
router.delete('/city/interest/:placeID/:cityID',async(ctx,next) => {
  let obj = ctx.params;
  let placeID = obj.placeID;
  let cityID = obj.cityID;
  let sql = `DELETE FROM place WHERE placeID = '${placeID}' and cityID = '${cityID}'`
   await connection.execute(sql); 
  ctx.body = JSON.stringify("success");
})

//周边推荐
router.delete('/city/enviro/:recomID/:cityID',async(ctx,next) => {
  let obj = ctx.params;
  let recomID = obj.recomID;
  let cityID = obj.cityID;
  let sql = `DELETE FROM recommend WHERE recomID = '${recomID}' and cityID = '${cityID}'`
  await connection.execute(sql); 
  ctx.body = JSON.stringify("success");
})
//美食推荐管理
router.delete('/city/food/:foodID/:cityID',async(ctx,next) => {
  let obj = ctx.params;
  let foodID = obj.foodID;
  let cityID = obj.cityID;
  console.log(obj)
  let sql = `DELETE FROM food WHERE foodID = '${foodID}' and cityID = '${cityID}'`
   await connection.execute(sql); 
  ctx.body = JSON.stringify("success");
})
//酒店推荐管理
router.delete('/city/hotel/:pubID/:cityID',async(ctx,next) => {
  let obj = ctx.params;
  let pubID = obj.pubID;
  let cityID = obj.cityID;
  let sql = `DELETE FROM pub WHERE pubID = '${pubID}' and cityID = '${cityID}'`
  console.log(sql)
  await connection.execute(sql); 
  ctx.body = JSON.stringify("success");
})
//帖子管理
router.delete('/post/:shareID',async(ctx,next) => {
  let obj = ctx.params;
  let shareID = obj.shareID;
  let sql = `DELETE FROM share WHERE shareID = '${shareID}'`
  await connection.execute(sql); 
  ctx.body = JSON.stringify("success");
})



/*********************查的操作**************************************/
router.post('/get',async (ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  delete obj.current;
  delete obj.pageSize;
  delete obj.sta
  for(let i in obj){
    str = str + `${i} Like'%${obj[i]}%' and `
  }
  let x = str.slice(0,str.length-5);
  let sql = `select * from user where ${x}`;
  console.log(x);
  console.log(sql)
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
  
})

//城市文化列表推荐
router.post('/city/citycul/get',async(ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  delete obj.current;
  delete obj.pageSize;
  delete obj.sta
  for(let i in obj){
    str = str + `${i} Like'%${obj[i]}%' and `
  }
  let x = str.slice(0,str.length-5);
  let sql = `select * from culture where ${x}`;
  console.log(x);
  console.log(sql)
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
  
})
//景点推荐
router.post('/city/interest/get',async(ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  delete obj.current;
  delete obj.pageSize;
  delete obj.sta
  for(let i in obj){
    str = str + `${i} Like'%${obj[i]}%' and `
  }
  let x = str.slice(0,str.length-5);
  let sql = `select * from place where ${x}`;
  console.log(x);
  console.log(sql)
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
  
})

//周边推荐管理
router.post('/city/enviro/get',async(ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  delete obj.current;
  delete obj.pageSize;
  delete obj.sta
  for(let i in obj){
    str = str + `${i} Like'%${obj[i]}%' and `
  }
  let x = str.slice(0,str.length-5);
  let sql = `select * from recommend where ${x}`;
  console.log(x);
  console.log(sql)
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
  
})

//美食推荐管理
router.post('/city/food/get',async(ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  delete obj.current;
  delete obj.pageSize;
  delete obj.sta
  for(let i in obj){
    str = str + `${i} Like'%${obj[i]}%' and `
  }
  let x = str.slice(0,str.length-5);
  let sql = `select * from food where ${x}`;
  console.log(x);
  console.log(sql)
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
  
})

//酒店推荐管理
router.post('/city/hotel/get',async(ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  delete obj.current;
  delete obj.pageSize;
  delete obj.sta
  for(let i in obj){
    str = str + `${i} Like'%${obj[i]}%' and `
  }
  let x = str.slice(0,str.length-5);
  let sql = `select * from pub where ${x}`;
  console.log(x);
  console.log(sql)
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
  
})

//帖子管理
router.post('/post/get',async(ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  delete obj.current;
  delete obj.pageSize;
  delete obj.sta
  for(let i in obj){
    str = str + `${i} Like'%${obj[i]}%' and `
  }
  let x = str.slice(0,str.length-5);
  let sql = `select * from share where ${x}`;
  console.log(x);
  console.log(sql)
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
  
})
//系统管理
router.post('/admin/get',async(ctx,next) => {
  let obj = ctx.request.body;
  let str = '';
  delete obj.current;
  delete obj.pageSize;
  delete obj.sta
  for(let i in obj){
    str = str + `${i} Like'%${obj[i]}%' and `
  }
  let x = str.slice(0,str.length-5);
  let sql = `select * from admin where ${x}`;
  console.log(x);
  console.log(sql)
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);

})
/************登录验证***************** */
router.post('/login',async (ctx,next) => {
  const admin = ctx.request.body;
  let count = 0;
  console.log(admin)
  let sql = 'SELECT * FROM admin';
  let res = await connection.execute(sql);
  let array = [];
  let record;
  let turn;
  for(var i in res[0]){
    let obj = {
      adminName:res[0][i].adminName,
      passwd:res[0][i].passwd
    }
    array.push(obj)
  }
  for(var i in res[0]){
    if(res[0][i].adminName == admin.username && res[0][i].passwd == admin.password){
      count++;
      record = i;
    }
    else{
      count+=0;
    }
  }
  if(count){
    turn = {
      status:'success',
      data:{
        username:res[0][record].adminName,
        adminImg:res[0][record].adminImg
      }
    }
  }else{
    turn = {status:'fail'}
  }

  ctx.body = JSON.stringify(turn);
})
  
/**************更新-改***************************/
//用户
router.put('/',async (ctx,next) => {
  let result = ctx.request.body;
  let newObj = result.new;
  let id = result.old.Userwxno;
  console.log(id)
  let str = '';
  for(let i in newObj){
    str = str + `${i} = '${newObj[i]}',`
  }
  let x = str.slice(0,str.length-1);
  let sql = `UPDATE user SET ${x} WHERE Userwxno = '${id}'`
  let res = await connection.execute(sql)
  ctx.body = JSON.stringify('success!')
})


//城市文化列表
router.put('/city/citycul',async(ctx,next)=>{
  let result = ctx.request.body;
  let newObj = result.new;
  let id0 = result.old.cultureID;
  let id1 = result.old.cityID;
  let str = '';
  for(let i in newObj){
    str = str + `${i} = '${newObj[i]}',`
  }
  let x = str.slice(0,str.length-1);
  let sql = `UPDATE culture SET ${x} WHERE cultureID = '${id0}' and cityID = '${id1}'`
  let res = await connection.execute(sql)
  ctx.body = JSON.stringify('success!')
})

//景点推荐管理
router.put('/city/interest',async(ctx,next)=>{
  let result = ctx.request.body;
  let newObj = result.new;
  let id0 = result.old.placeID;
  let id1 = result.old.cityID;
  let str = '';
  for(let i in newObj){
    str = str + `${i} = '${newObj[i]}',`
  }
  let x = str.slice(0,str.length-1);
  let sql = `UPDATE place SET ${x} WHERE placeID = '${id0}' and cityID = '${id1}' `
  console.log(sql)
  let res = await connection.execute(sql)
  ctx.body = JSON.stringify('success!')
})

//周边
router.put('/city/enviro',async(ctx,next)=>{
  let result = ctx.request.body;
  let newObj = result.new;
  let id0 = result.old.recomID;
  let id1 = result.old.cityID;
  let str = '';
  for(let i in newObj){
    str = str + `${i} = '${newObj[i]}',`
  }
  let x = str.slice(0,str.length-1);
  let sql = `UPDATE recommend SET ${x} WHERE recomID = '${id0}' and cityID = '${id1}'`
  let res = await connection.execute(sql)
  ctx.body = JSON.stringify('success!')
})
//美食
router.put('/city/food',async(ctx,next)=>{
  let result = ctx.request.body;
  let newObj = result.new;
  let id0 = result.old.foodID;
  let id1 = result.old.cityID;
  let str = '';
  for(let i in newObj){
    str = str + `${i} = '${newObj[i]}',`
  }
  let x = str.slice(0,str.length-1);
  let sql = `UPDATE food SET ${x} WHERE foodID = '${id0}' and cityID = '${id1}'`
  console.log(sql)
  let res = await connection.execute(sql)
  ctx.body = JSON.stringify('success!')
})
//酒店
router.put('/city/pub',async(ctx,next)=>{
  let result = ctx.request.body;
  let newObj = result.new;
  let id0 = result.old.pubID;
  let id1 = result.old.cityID;
  let str = '';
  for(let i in newObj){
    str = str + `${i} = '${newObj[i]}',`
  }
  let x = str.slice(0,str.length-1);
  let sql = `UPDATE pub SET ${x} WHERE pubID = '${id0}' and cityID = '${id1}'`
  let res = await connection.execute(sql)
  ctx.body = JSON.stringify('success!')
})
//帖子
// router.put('/city/post',async(ctx,next)=>{
//   let result = ctx.request.body;
//   let newObj = result.new;
//   let id = result.old.shareID;
//   let str = '';
//   for(let i in newObj){
//     str = str + `${i} = '${newObj[i]}',`
//   }
//   let x = str.slice(0,str.length-1);
//   let sql = `UPDATE share SET ${x} WHERE Userwxno = '${id}'`
//   let res = await connection.execute(sql)
//   ctx.body = JSON.stringify('success!')
// })

//图片的action
router.post('/pic',async(ctx,next) => {
  const file = ctx.request.files.upload;
  const reader = fs.createReadStream(file.path);
  const filePath = path.join(__dirname,'images') + `\\${file.name}`;
  const upStream = fs.createWriteStream(filePath);
  reader.pipe(upStream)
  let pic = filePath.slice();
  let realPath = `http://127.0.0.1:8080/images/${file.name}`
  console.log(realPath)
  ctx.body = {Type:200,status:'上传成功',addr:realPath}
})


router.get('/images/:name',async(ctx,next) => {
  let name = ctx.params.name;
  let filePath = `./images/${name}`
  let data = fs.readFileSync(filePath);
  let mimeType = mime.lookup(filePath);
  ctx.set('content-type',mimeType);
  ctx.body = data;
})






app.use(router.routes())
app.listen(8080);